Shell脚本实战07-MySQL数据库分库分表备份

发表于 | 更新于: | 分类于

1. 需求

实现MySQL分库分表备份的脚本。

2. 实现过程

2.1. 批量建表并插入数据

准备测试数据：通过写脚本批量建表并插入数据。

1
2
3
4
5
6
7
8
9
10
#!/bin/bash
PATH="/usr/local/mysql/bin:$PATH"
MYUSER=root
MYPASS=123456
SOCKET=/tmp/mysql.sock
MYCMD="mysql -u$MYUSER -p$MYPASS -S $SOCKET"	#<==定义登陆数据库的命令
for dbname in db1 db2 db3 db4
do		#<==批量建表及插入数据
	$MYCMD -e "use $dbname; create table test(id int,name varchar(16)); insert into test values(1,'testdata');" 2> /dev/null
done

2.2. 查看测试结果

使用如下脚本查看测试数据结果

1
2
3
4
5
6
7
8
9
10
11
12
#!/bin/bash
PATH="/usr/local/mysql/bin:$PATH"
MYUSER=root
MYPASS=123456
SOCKET=/tmp/mysql.sock
MYCMD="mysql -u$MYUSER -p$MYPASS -S $SOCKET"
for dbname in db1 db2 db3 db4
do	#<==批量查看数据
	echo ============${dbname}.test===============
	$MYCMD -e "use $dbname; select * from ${dbname}.test;" 2> /dev/null
done

查看到的结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
============db1.test===============
+------+----------+
| id   | name     |
+------+----------+
|    1 | testdata |
+------+----------+
============db2.test===============
+------+----------+
| id   | name     |
+------+----------+
|    1 | testdata |
+------+----------+
============db3.test===============
+------+----------+
| id   | name     |
+------+----------+
|    1 | testdata |
+------+----------+
============db4.test===============
+------+----------+
| id   | name     |
+------+----------+
|    1 | testdata |
+------+----------+

2.3. 分库分表备份的最终实现脚本

MySQL数据库分库分表备份的实现脚本如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#!/bin/bash
PATH="/usr/local/mysql/bin:$PATH"
DBPATH=/data/backup
MYUSER=root
MYPASS=123456
SOCKET=/tmp/mysql.sock
MYCMD="mysql -u$MYUSER -p$MYPASS -S $SOCKET"
MYDUMP="mysqldump -u$MYUSER -p$MYPASS -S $SOCKET"
[ ! -d "$DBPATH" ] && mkdir $DBPATH
for dbname in `$MYCMD -e "show databases;" | sed '1,2d' | egrep -v "mysql|schema"`	#<==登陆数据库获取数据库里的所有数据库名
do
	mkdir $DBPATH/${dbname}_$(date +%F) -p	#<==创建对应目录
	for table in `$MYCMD -e "show tables from $dbname;" | sed '1d'`
	#<==内层循环，获取每个库里的所有表，然后进入循环
	do
		$MYDUMP $dbname $table | gzip > $DBPATH/${dbname}_$(date +%F)/${dbname}_${table}.sql.gz 2> /dev/null
#<==备份指定的库内的表到指定目录下，并以库表名字命名备份的名字
	done
done

脚本执行结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
# tree /data/backup/
/data/backup/
├── db1_2018-03-18
│   └── db1_test.sql.gz
├── db2_2018-03-18
│   └── db2_test.sql.gz
├── db3_2018-03-18
│   └── db3_test.sql.gz
└── db4_2018-03-18
    └── db4_test.sql.gz
4 directories, 4 files

0%